/*
 * EmployeeDAO.java
 *
 * Created on March 5, 2007, 8:07 PM
 *
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.handlers.*;
import java.sql.*;
import java.util.ArrayList;

/**Reads and writes EmployeeBO data to and from the database.
 *
 * @author Travis Kulbeth
 */
public class EmployeeDAO {
    
    /** Contains an instance of EmployeeDAO*/
    private static EmployeeDAO instance = null;
    
    /** Creates a new instance of CouponDAO */
    public EmployeeDAO() {
    }
    
    /**Retrieves an instance of the EmployeeDAO object
     *
     * @return a current singleton instance of the EmployeeDAO class
     */
    public static synchronized EmployeeDAO getInstance() {
        if (instance == null) {
            instance = new EmployeeDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     * Creates a new EmployeeBO object with a newly generated GUID.
     * 
     * @return a newly created EmployeeBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public EmployeeBO create() throws Exception{
        String id = GUID.generate();
        EmployeeBO bo = new EmployeeBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getEmployeeID(), bo);
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing Employee record
     * from the database based on the Employee id and returns a EmployeeBO.
     * 
     * @return a EmployeeBO containing the information extracted from the requested
     * tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized EmployeeBO read(String id) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        EmployeeBO emp = (EmployeeBO)c.get(id);
        if (emp != null){
            return emp;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            emp = this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for Employee ID = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return emp;
    }
    
    
    /**
     * This is the public read statement.  It loads an existing Employee record
     * from the database based on the Employee id, using an already existing connection to the DB,
     * and returns a EmployeeBO.
     * 
     * @return EmployeeBO
     * @param id contains the given id for the Employee to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    synchronized EmployeeBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        EmployeeBO emp = (EmployeeBO)c.get(id);
        if (emp != null){
            return emp;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Employee WHERE EmployeeID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            emp = new EmployeeBO(rs.getString("EmployeeID"));
            emp.setEmpFirstName(rs.getString("empFirstName"));
            emp.setEmpMI(rs.getString("empMI"));
            emp.setEmpLastName(rs.getString("empLastName"));
            emp.setEmpAddress(rs.getString("EmpAddress"));
            emp.setEmpCity(rs.getString("EmpCity"));
            emp.setEmpState(rs.getString("EmpState"));
            emp.setEmpZip(rs.getString("EmpZip"));
            emp.setEmpPhone(rs.getString("empPhone"));
            emp.setEmpSocSecNum(rs.getString("empSSN"));
            emp.setWageRate(rs.getDouble("wageRate"));
            emp.setHireDate(rs.getDate("HireDate").toString());
            emp.setPassword(rs.getString("Password"));
            //set the fire date accordingly
            if(rs.getDate("FireDate") != null){
                emp.setFireDate(rs.getDate("FireDate").toString());
            }else{
                String fDate = null;
                emp.setFireDate(fDate);
            }
            emp.setAlreadyInDB(true);
            emp.setIsDirty(false);
            
            //get the Position of the employee
            if(rs.getString("PositionID") != null){
                emp.setPosition(PositionDAO.getInstance().read(rs.getString("PositionID")));
            }
            
            // save to the cache
            c.put(emp.getEmployeeID(), emp);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return emp;
    }
 
        /**
     * This is a public read statement.  It finds an employee record by username (i.e., from the login screen)
     * and returns a EmployeeBO.
     * 
     * @return EmployeeBO
     * @param uname contains the given id for the Employee to be read
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized EmployeeBO readByUserName(String uname) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        EmployeeBO emp = (EmployeeBO)c.get(uname);
        if (emp != null){
            return emp;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Employee WHERE UserName = '" + uname + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            emp = new EmployeeBO(rs.getString("EmployeeID"));
            emp.setEmpFirstName(rs.getString("empFirstName"));
            emp.setEmpMI(rs.getString("empMI"));
            emp.setEmpLastName(rs.getString("empLastName"));
            emp.setEmpAddress(rs.getString("EmpAddress"));
            emp.setEmpCity(rs.getString("EmpCity"));
            emp.setEmpState(rs.getString("EmpState"));
            emp.setEmpZip(rs.getString("EmpZip"));
            emp.setEmpPhone(rs.getString("empPhone"));
            emp.setEmpSocSecNum(rs.getString("empSSN"));
            emp.setWageRate(rs.getDouble("wageRate"));
            emp.setHireDate(rs.getDate("HireDate").toString());
            emp.setPassword(rs.getString("Password"));
            //set the fire date accordingly
            if(rs.getDate("FireDate") != null){
                emp.setFireDate(rs.getDate("FireDate").toString());
            }else{
                String fDate = null;
                emp.setFireDate(fDate);
            }
            emp.setAlreadyInDB(true);
            emp.setIsDirty(false);
            
            //get the Position of the employee
            if(rs.getString("PositionID") != null){
                emp.setPosition(PositionDAO.getInstance().read(rs.getString("PositionID")));
            }
            
            // save to the cache
            c.put(emp.getEmployeeID(), emp);
            ConnectionPool.getInstance().release(conn);
        }else{
            throw new DataException("Employee was not found in the database.");
        }
        stmt.close();
        // return the BO
        return emp;
    }
    
    /**
     * This is a public method to retrieve a list of all Employees in the DB.
     * 
     * @return a list of all Employees from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<EmployeeBO> readAllEmployees() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<EmployeeBO> empList = new ArrayList<EmployeeBO>();
        //now build the query
        String query = "SELECT * FROM Employee";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        //build the object from the result set
        while (rs.next()) {
            EmployeeBO emp = new EmployeeBO(rs.getString("EmployeeID"));
            emp.setEmpFirstName(rs.getString("empFirstName"));
            emp.setEmpMI(rs.getString("empMI"));
            emp.setEmpLastName(rs.getString("empLastName"));
            emp.setEmpAddress(rs.getString("EmpAddress"));
            emp.setEmpCity(rs.getString("EmpCity"));
            emp.setEmpState(rs.getString("EmpState"));
            emp.setEmpZip(rs.getString("EmpZip"));
            emp.setEmpPhone(rs.getString("empPhone"));
            emp.setEmpSocSecNum(rs.getString("empSSN"));
            emp.setWageRate(rs.getDouble("wageRate"));
            emp.setHireDate(rs.getDate("HireDate").toString());
            emp.setPassword(rs.getString("Password"));
            //set the fire date accordingly
            if(rs.getDate("FireDate") != null){
                emp.setFireDate(rs.getDate("FireDate").toString());
            }else{
                String fDate = null;
                emp.setFireDate(fDate);
            }
            emp.setAlreadyInDB(true);
            emp.setIsDirty(false);
            
            //get the Position of the employee
            if(rs.getString("PositionID") != null){
                emp.setPosition(PositionDAO.getInstance().read(rs.getString("PositionID")));
            }
            
            // save to the list
            empList.add(emp);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return empList;
    }
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) wants to save or update an object
     * into the database.
     * 
     * @param empIn is a EmployeeBO object to be saved
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized void save(EmployeeBO empIn) throws DataException, ConnectionPoolException {
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(empIn, conn);                                   // call save with a connection
                conn.commit();
            } catch (ConnectionPoolException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                ex.printStackTrace();
                throw new SQLException();
            }
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Error saving record for Employee ID =" + empIn.getEmployeeID(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        return;
    }
    
    /**
     * This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * 
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the EmployeeDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.
     * 
     * @param emp contains the EmployeeBO to be saved
     * @param conn contains the Connection that has been established to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    synchronized void save(EmployeeBO emp, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // run update or insert
        if (emp.isIsDirty()) {
            if (emp.isAlreadyInDB()) {
                update(emp, conn);
            }else{
                insert(emp, conn);
            }
            // set the dirty flag to false now that we've saved it
            emp.setIsDirty(false);
        }
        
        
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(emp.getEmployeeID(), emp);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * @param emp contains the EmployeeBO to be updated to the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    private synchronized void update(EmployeeBO emp, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Employee SET EmpFirstName = ?, EmpMI = ?, " +
                "EmpLastName = ?, EmpAddress = ?, EmpCity = ?, EmpState = ?, EmpZip = ?, empPhone = ?, empSSN = ?, " +
                "PositionID = ?, WageRate = ?, HireDate = ?, FireDate = ?, Password = ? WHERE EmployeeID = ?");
        pstmt.setString(1, emp.getEmpFirstName());
        pstmt.setString(2, emp.getEmpMI());
        pstmt.setString(3, emp.getEmpLastName());
        pstmt.setString(4, emp.getEmpAddress());
        pstmt.setString(5, emp.getEmpCity());
        pstmt.setString(6, emp.getEmpState());
        pstmt.setString(7, emp.getEmpZip());
        pstmt.setString(8, emp.getEmpPhone());
        pstmt.setString(9, emp.getEmpSocSecNum());
        //set the position ID
        if(emp.getPosition() != null){
            pstmt.setString(10, emp.getPosition().getPositionID());
            PositionDAO.getInstance().save(emp.getPosition(), conn);
        }else{
            pstmt.setNull(10, Types.VARCHAR);
        }
        pstmt.setDouble(11, emp.getWageRate());
        pstmt.setDate(12, java.sql.Date.valueOf(emp.getHireDate()));
        //set the fire date as applicable
        if( emp.getFireDate() != null){
            pstmt.setDate(13, java.sql.Date.valueOf(emp.getFireDate()));
        }else{
            pstmt.setNull(13, Types.VARCHAR);
        }
        pstmt.setString(14, emp.getPassword());
        pstmt.setString(15, emp.getEmployeeID());
        pstmt.executeUpdate();
        pstmt.close();
        emp.setAlreadyInDB(true);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * @param emp contains the EmployeeBO to be updated to the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    private synchronized void insert(EmployeeBO emp, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Employee (EmployeeID, EmpFirstName, EmpMI, " +
                "EmpLastName, EmpAddress, EmpCity, EmpState, EmpZip, EmpPhone, EmpSSN, PositionID, WageRate, " +
                "HireDate, FireDate, Password) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, emp.getEmployeeID());
        pstmt.setString(2, emp.getEmpFirstName());
        pstmt.setString(3, emp.getEmpMI());
        pstmt.setString(4, emp.getEmpLastName());
        pstmt.setString(5, emp.getEmpAddress());
        pstmt.setString(6, emp.getEmpCity());
        pstmt.setString(7, emp.getEmpState());
        pstmt.setString(8, emp.getEmpZip());
        pstmt.setString(9, emp.getEmpPhone());
        pstmt.setString(10, emp.getEmpSocSecNum());
        //set the position ID
        if(emp.getPosition() != null){
            pstmt.setString(11, emp.getPosition().getPositionID());
            PositionDAO.getInstance().save(emp.getPosition(), conn);
        }else{
            pstmt.setNull(11, Types.VARCHAR);
        }
        pstmt.setDouble(12, emp.getWageRate());
        pstmt.setDate(13, java.sql.Date.valueOf(emp.getHireDate()));
        //set the fire date as applicable
        if( emp.getFireDate() != null){
            pstmt.setDate(14, java.sql.Date.valueOf(emp.getFireDate()));
        }else{
            pstmt.setNull(14, Types.VARCHAR);
        }
        pstmt.setString(15, emp.getPassword());
        pstmt.executeUpdate();
        pstmt.close();
        // tell the object that it's now in the db (so we call update next time not insert)
        emp.setAlreadyInDB(true);
    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    /** I did not include the DELETE functionality intentionally due to instructions received, as well as
     * to protect the integrity of the data.
     */
    
}
